﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using KVB.Common;

namespace Customer_Profile.eCard
{
    public partial class ImportSettle : System.Web.UI.Page
    {
        string filename = "";
        DataSet ds = new DataSet();
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            filename = fileupdown();
            hidFileName.Value = filename;
            fillGrid(filename, "Data");
        }
        public string fileupdown()
        {
            if (Path.GetExtension(FileUpload1.FileName) == ".txt" || Path.GetExtension(FileUpload1.FileName) == ".xlsx" || Path.GetExtension(FileUpload1.FileName) == ".tsv")
            {
                string filename = FileUpload1.FileName; //FileUpload1.PostedFile.FileName.Substring(FileUpload1.PostedFile.FileName.LastIndexOf("\\"));
                FileUpload1.PostedFile.SaveAs(Server.MapPath("fileupdown\\") + filename);//上传文件
                FileUpload1.PostedFile.InputStream.Close();
                lblWarning.Text = "数据上传成功";
                Response.Write("<script>alert('数据上传成功！');</script>");
                return filename;
            }
            else
            {
                Response.Write("<script language='Javascript'>alert('请选择txt xlsx tsv文件！');history.go(-1);</script>");
                return null;
            }
        }

        public void fillGrid(string filename, string excelName)
        {
            string conn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Server.MapPath("fileupdown\\") + filename + ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件  
            string sqlin = "SELECT * FROM [" + excelName + "$]";
            OleDbCommand olecommand = new OleDbCommand(sqlin, new OleDbConnection(conn));
            OleDbDataAdapter adapterin = new OleDbDataAdapter(olecommand);
            //DataSet dsin = new DataSet();
            adapterin.Fill(ds);
            this.GridView1.DataSource = ds.Tables[0];
            this.GridView1.DataBind();
        }

        protected void Button2_Click(object sender, EventArgs e)
        {
            fillGrid(hidFileName.Value, "Data");


            #region 
           
            SqlConnection conn = null;
            DB.Connection(ref conn);
            SqlTransaction trans = conn.BeginTransaction();
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.Connection = conn;
            sqlCommand.Transaction = trans;

            try
            {
                ds.Tables[0].Rows[2].Delete();
                ds.Tables[0].Rows[1].Delete();
                ds.Tables[0].Rows[0].Delete();
                ds.AcceptChanges();
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    //添加客户信息
                    sqlCommand.Parameters.Clear();
                    string cparty_name = dr[1].ToString();
                    string card_no = dr[2].ToString();
                    string settle_agent_name = dr[3].ToString();
                    string settle_input_date = dr[4].ToString();
                    string settle_date = dr[5].ToString();
                    string settle_amount =decimal.Parse(dr[6].ToString()).ToString();
                    string eBank_fee = decimal.Parse(dr[7].ToString()).ToString();
                    string settle_charge = decimal.Parse(dr[8].ToString()).ToString();
                    string settle_remark = dr[9].ToString();
                   
                    //sqlCommand.Parameters.Add("@cparty_name", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@card_no", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_agent_name", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_input_date", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_date", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_amount", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@eBank_fee", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_charge", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@settle_remark", SqlDbType.NVarChar, 500);

                    sqlCommand.Parameters["@card_no"].Value = card_no;
                    sqlCommand.Parameters["@settle_agent_name"].Value = settle_agent_name;
                    sqlCommand.Parameters["@settle_input_date"].Value = settle_input_date;
                    sqlCommand.Parameters["@settle_date"].Value = settle_date;
                    sqlCommand.Parameters["@settle_amount"].Value = settle_amount;
                    sqlCommand.Parameters["@eBank_fee"].Value = eBank_fee;
                    sqlCommand.Parameters["@settle_charge"].Value = settle_charge;
                    sqlCommand.Parameters["@settle_remark"].Value = settle_remark;

                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "eCard_mmdeal_SettleUpdate";
                    sqlCommand.ExecuteNonQuery();
                }
                trans.Commit();
                Response.Write("<script>alert('导入数据成功！');</script>");
                lblWarning.Text = "导入数据成功";
            }
            catch (Exception ex)
            {
                lblWarning.Text = "导入数据出错"+ex.ToString();
                KVB.Common.Log.WriteDefaultLog("MMDeals.SettleUpdate", ex.ToString());
                trans.Rollback();
                //Response.Write("<script>alert('导入数据出错！'+" + ex.ToString() + ");</script>");
            }
            finally
            {
                DB.ConnDispose(ref conn);
                trans.Dispose();
                conn.Dispose();
            }

            #endregion
            #region old
            //sql(filename, "Data", "");
            /*
            DataTable dtin = dsin.Tables[0];//连接并读取excel数据
            //if (!existedName(dsin.Tables[0].Rows[1].ToString()))
            //{
            for (int i = 0; i < dsin.Tables[0].Rows.Count; i++)//将Excel文件中第一个工作表的数据导入到sql数据库scjd_youliaoxiaohaojihua表中
            {
                string sql = "insert into " + tablename + " values('" + dsin.Tables[0].Rows[i]["订单流水"] + "','" + dsin.Tables[0].Rows[i]["用户号码"] + "','" + dsin.Tables[0].Rows[i]["用户品牌"] + "','" + dsin.Tables[0].Rows[i]["用户县市"] + "','" + dsin.Tables[0].Rows[i]["订单主题"] + "' ,'" + dsin.Tables[0].Rows[i]["订单类型"] + "','" + dsin.Tables[0].Rows[i]["订单内容"] + "','" + dsin.Tables[0].Rows[i]["紧急程度"] + "','" + dsin.Tables[0].Rows[i]["订单状态"] + "','" + dsin.Tables[0].Rows[i]["受理时间"] + "','" + dsin.Tables[0].Rows[i]["要求处理时限"] + "' ,'" + dsin.Tables[0].Rows[i]["受理部门"] + "','" + dsin.Tables[0].Rows[i]["受理人"] + "','" + dsin.Tables[0].Rows[i]["处理过程"] + "','" + dsin.Tables[0].Rows[i]["是否当场归档"] + "','" + dsin.Tables[0].Rows[i]["归档时间"] + "','" + dsin.Tables[0].Rows[i]["归档部门"] + "','" + dsin.Tables[0].Rows[i]["归档人"] + "','" + dsin.Tables[0].Rows[i]["责任原因"] + "','" + dsin.Tables[0].Rows[i]["责任方"] + "','" + dsin.Tables[0].Rows[i]["满意度"] + "','" + dsin.Tables[0].Rows[i]["解决程度"] + "')";
                try
                {
                    page.DoSql(sql);
                }
                catch (Exception ex)
                {
                    Response.Write("<script>alert('数据导入错误，请检查Excel文件')</script>");
                    return;
                }
                //}
            }
            FileInfo file = new FileInfo(Server.MapPath("fileupdown/") + filename);
            if (file.Exists)
            {//删除文件
                file.Delete();
            }
             * */
            #endregion
        }
    }
}